package com.helper;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBHelper {
	
//	private final String dbUser = "root";
//	private final String dbPwd = "12345";
//	private final String dbUrl = "jdbc:mysql://127.0.0.1:3307/";
//	private final String dbName = "examdb";

	private String dbUser = "root";
	private String dbPwd = "";
	private String dbUrl = "jdbc:mysql://127.0.0.1:3306/";
	private String dbName = "examdb";
	
	private static Connection connection = null;

	public DBHelper() {

		try {
//			FileInputStream input = new FileInputStream("./DBcfg.txt");
//			BufferedReader br = new BufferedReader(new InputStreamReader(input));
//			dbUser = br.readLine();
//			dbPwd = br.readLine();
//			dbUrl = br.readLine();
//			dbName = br.readLine();
//			br.close();
//			input.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void connect() {
		try {
			if(connection == null) {
				Class.forName("com.mysql.jdbc.Driver");
				connection = DriverManager.getConnection(dbUrl + dbName, dbUser, dbPwd);
			}
		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public List<Map<String, Object>> query(String sql, List<Object> param) {
		
		List<Map<String, Object>> result = 
				new ArrayList<Map<String,Object>>();
		if(connection == null) {
			connect();
		}
		try {
			PreparedStatement pst =
					connection.prepareStatement(sql);
			if(param != null) {
				for(int i=0; i<param.size(); ++i) {
					pst.setObject(i+1, param.get(i));
				}
			}
			ResultSet data = pst.executeQuery();
			ResultSetMetaData metaData = data.getMetaData();
			int cols = metaData.getColumnCount();
			while(data.next()) {
				Map<String,Object> map = new HashMap<String,Object>();
				for(int i=0; i<cols; ++i) {
					String colName = metaData.getColumnName(i+1);
					Object obj = data.getObject(colName);
					if(obj == null) {  obj = ""; }
					map.put(colName, obj);
				}
				result.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	public boolean update(String sql, List<Object> param) {
		if(connection == null) {
			connect();
		}
		try {
			PreparedStatement pst =
					connection.prepareStatement(sql);
			if(param != null) {
				for(int i=0; i<param.size(); ++i) {
					pst.setObject(i+1, param.get(i));
				}
			}
			return pst.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
